﻿using System;
using Login.Model;

namespace Login.ADL
{
    public class tb_Sweek
    {
        /// <summary>
        /// 通过班级ID找到对应班级
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        #region
        public static UseModel getClassName(string classId)
        {
            //构造搜索班级名称的SQL语句
            string sql = "select ClassName from tb_Class where ClassId='{0}'";
            sql = string.Format(sql, classId);
            //得到班级名称
            string className = Helpsql.getgeti(sql);
            UseModel use = new UseModel();
            use.banJi = className;
            return use;
        }
        #endregion

        /// <summary>
        /// 指定专业不上课周个数
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        #region
        public static int getWeekNum(string id)
        {
            int a = 0;
            //循环遍历所有周数
            for (int i = 2; i < 17; i++)
            {
                string zhou = "Week" + i;
                //构造不上课周个数的sql语句
                string sql = "select count(*) from tb_NoClass where {0}='不排课' and ClassId='{1}' ";
                sql = string.Format(sql, zhou, id);
                //不上课周个数
                int n = Helpsql.getshu(sql);
                if (n != 0)
                {
                    a++;
                }
            }
            return a;
        }
        #endregion

        /// <summary>
        /// 不上课周数组提取
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        #region
        public static string[] getWeeks(string id)
        {
            int b = 0;
            //指定专业不上课周个数
            int a = getWeekNum(id);
            //实例化一个不上课周的容器
            string[] str=new string[a];
            //循环遍历所有周数
            for (int i = 2; i < 17; i++)
            {
                string zhou = "Week" + i;
                //构造不上课周个数的sql语句
                string sql = "select count(*) from tb_NoClass where {0}='不排课' and ClassId='{1}' ";
                sql = string.Format(sql, zhou, id);
                //不上课周个数
                int n = Helpsql.getshu(sql);
                if (n != 0)
                {
                    str[b] = Convert.ToString(i);
                    b++;
                }
            }
            return str;
        }
        #endregion


        /// <summary>
        /// 存入指定班级不上课周
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="n"></param>
        #region
        public static void getcun(string classId, int n)
        {
            string zhou = "Week" + n;
            //构造不上课周存入的sql语句
            string sql = "update tb_NoClass set {0}='不排课' where ClassId='{1}' ";
            sql = string.Format(sql, zhou, classId);
            //不上课周存入
            Helpsql.getxiu(sql);
        }
        public static void UpdateEmpty(string id)
        {
            for (int i = 2; i < 17; i++)
            {
                string zhou = "Week" + i;
                //构造不上课周清除的sql语句
                string sql = "update tb_NoClass set {0}='' where ClassId='{1}' ";
                sql = string.Format(sql, zhou, id);
                //不上课周清除
                Helpsql.getxiu(sql);
            }
        }
        #endregion 


        /// <summary>
        /// 班级实验数提取
        /// </summary>
        /// <param name="banjiId"></param>
        /// <returns></returns>
        #region
        public static int[] getExpermentNumZu(string banjiId)
        {
            tb_shiyanclass tys = new tb_shiyanclass();
            //对应班级实验数
            int a = tys.getExpermentShu(tb_shangkeClass.getClassName(banjiId));
            //实例化数组
            int[] arr=new int[a];
            //构造搜索对应班级所有实验的sql语句
            string sql = "select * from tb_Class_Exp_Details where ClassId='{0}'";
            sql = string.Format(sql, banjiId);
            //得到对应班级所上实验的ID
            arr = Helpsql.getZuOne(sql, a);
            return arr;
        }
        #endregion



        /// <summary>
        /// 判断周序号以及另一半对应的周序号的列中是否有约束
        /// </summary>
        /// <param name="BanJiMing">班级名</param>
        /// <param name="k">周序号</param>
        /// <returns></returns>
        #region
        public static int PaiKeFou(string BanJiMing, int k)
        {
            string Week = "Week" + k;
            //构造当前周是否有约束的sql语句
            string sql = "select count(*) from tb_NoClass where ClassId='{0}' and {1}='不排课'";
            sql = string.Format(sql,BanJiMing,Week);
            int i = Helpsql.getshu(sql);
            k = k + 7;
            Week = "Week" + k;
            sql = "select count(*) from tb_NoClass where ClassId='{0}' and {1}='不排课'";
            sql = string.Format(sql, BanJiMing, Week);
            i =i+ Helpsql.getshu(sql);
            return i;
        }


        /// <summary>
        /// 将实验名插入到排课表中
        /// </summary>
        /// <param name="zhou">周列</param>
        /// <param name="n">实验ID</param>
        /// <param name="id">班级ID</param>
        /// <param name="a">组数</param>
        public static void shangKeZhouCunQu(string zhou,int n,string id,int a)
        {
            string shiyan = Helpsql.xueShuZhaoShiYan(n);
            //构造插入到排课表中的sql语句
            string sql = "update tb_NoClass set {0}='{1}' where ClassId='{2}' and GroupId='{3}'";
            sql = string.Format(sql,zhou,shiyan,id,a);
            Helpsql.getxiu(sql);
        }

        /// <summary>
        /// 判断班级对应的周是否有实验课
        /// </summary>
        /// <param name="zhou">周数</param>
        /// <param name="BanJiId">班级id</param>
        /// <returns></returns>
        public static int ZhouShiYanFou(string zhou, int BanJiId)
        {
            //构造班级对应周的实验课数的sql语句
            string sql = "select count(*) from tb_NoClass where ClassId='{0}' and GroupId='1' and {1} !='' ";
            sql = string.Format(sql, BanJiId,zhou);
            return Helpsql.getshu(sql);
        }

        public static string ShiYanMingTiqu(int BanJiId, string zhou, int ZuShu)
        {
            string sql = "select {0} from tb_NoClass where ClassId='{1}' and GroupId='{2}'";
            sql = string.Format(sql, zhou,BanJiId,ZuShu);
            return Helpsql.getgeti(sql);
        }
        public static void ShiYanDelete(int BanJiId,int k,int a)
        {
            string Week = "Week" + k;
            string sql = "update tb_NoClass set {0}='' where ClassId='{1}'";
            sql = string.Format(sql, Week,BanJiId);
            Helpsql.getxiu(sql);
            k = k + 8;
            Week = "Week" + k;
            sql = "update tb_NoClass set {0}='' where ClassId='{1}'";
            sql = string.Format(sql, Week, BanJiId);
            Helpsql.getxiu(sql);
            Week = "Week" + a;
            sql = "update tb_NoClass set {0}='' where ClassId='{1}'";
            sql = string.Format(sql, Week, BanJiId);
            Helpsql.getxiu(sql);
        }
        public static void ShiYanHuiFu(int BanJiId, int a,string ShiYanTwo,string ShiYanThree)
        {
            string Week = "Week" + a;
            string sql = "update tb_NoClass set {0}='{1}' where ClassId='{2}' and GroupId='1'";
            sql = string.Format(sql,Week,ShiYanTwo,BanJiId);
            Helpsql.getxiu(sql);
            sql = "update tb_NoClass set {0}='{1}' where ClassId='{2}' and GroupId='2'";
            sql = string.Format(sql, Week, ShiYanThree, BanJiId);
            Helpsql.getxiu(sql);
        }

        public static void ShiYanHuiFu1(int BanJiId, int k, string ShiYanOne)
        {
            string Week = "Week" + k;
            string sql = "update tb_NoClass set {0}='{1}' where ClassId='{2}' and GroupId='1'";
            sql = string.Format(sql, Week, ShiYanOne, BanJiId);
            Helpsql.getxiu(sql);
            k = k + 8;
            Week = "Week" + k;
            sql = "update tb_NoClass set {0}='{1}' where ClassId='{2}' and GroupId='2'";
            sql = string.Format(sql, Week, ShiYanOne, BanJiId);
            Helpsql.getxiu(sql);
        }

        /// <summary>
        /// 判断在同一时间同一周中是否含有相关的实验名
        /// </summary>
        /// <param name="TimeId">时间id</param>
        /// <param name="zhou">周名</param>
        /// <param name="shiyan">实验名</param>
        /// <returns></returns>
        public static int ShiYanCunFou(int TimeId,string zhou,string shiyan)
        {
            //构造在同一时间同一周中有相关的实验名数的sql语句
            string sql = "select count(*) from tb_NoClass where TimeId='{0}' and {1}='{2}' ";
            sql = string.Format(sql,TimeId, zhou,shiyan);
            return Helpsql.getshu(sql);
        }

        public static int NzhouShiYan(string zhou, string ShiYanOne, string ShiYanThree, int TimeId, int BanJiId)
        {
            string sql = "select count(*) from tb_NoClass where TimeId='{0}' and ClassId !='{1}' and {2}='{3}' ";
            sql = string.Format(sql, TimeId, BanJiId, zhou, ShiYanOne);
            int a= Helpsql.getshu(sql);
            sql = string.Format(sql, TimeId, BanJiId, zhou, ShiYanThree);
            int b = Helpsql.getshu(sql);
            int n = a + b;
            return n;
        }


        public static void ShiYanCunRu(int BanJiId,string shiyan,int GroupShu,string zhou)
        {
            string sql = "update tb_NoClass set {0}='{1}' where ClassId='{2}' and GroupId='{3}' ";
            sql = string.Format(sql, zhou, shiyan,BanJiId,GroupShu);
            Helpsql.getxiu(sql);
        }

        /// <summary>
        /// 时间点对应周相应的实验数
        /// </summary>
        /// <param name="k"></param>
        /// <param name="TimeId"></param>
        /// <returns></returns>
        public static int WeekSYshu(int k,int TimeId) 
        {
            string zhou = "Week" + k;
            string sql = "select count(*) from tb_NoClass where TimeId='{0}' and {1} !='' ";
            sql = string.Format(sql, TimeId, zhou);
            return Helpsql.getshu(sql);
        }

        /// <summary>
        /// 得到时间点上第十七周的所有实验
        /// </summary>
        /// <param name="k">周序号</param>
        /// <param name="TimeId">时间ID</param>
        /// <param name="n">实验数</param>
        /// <returns></returns>
        public static string[] ShiYanMingQu(int k,int TimeId,int n)
        {
            string[] arr=new string[n];
            string zhou = "Week" + k;
            //构造时间点上第十七周的所有实验的sql语句
            string sql = "select Week15,Week16,{0} from tb_NoClass where TimeId='{1}' and {2} !=''";
            sql = string.Format(sql, zhou,TimeId,zhou);
            arr = Helpsql.getzu(sql,n);
            return arr;
        }

        /// <summary>
        /// 通过周实验找到相关班级
        /// </summary>
        /// <param name="shiyan">实验名称</param>
        /// <param name="TimeId">时间ID</param>
        /// <returns></returns>
        public static int CalssId(string shiyan, int TimeId)  
        {
            //构造通过周实验找到相关班级的sql语句
            string sql = "select ClassId from tb_NoClass where TimeId='{0}' and Week17 ='{1}'";
            sql = string.Format(sql, TimeId,shiyan);
            string ShiYanId = Helpsql.getgeti(sql);
            int ShiYan = Convert.ToInt32(ShiYanId);
            return ShiYan;
        }

        /// <summary>
        /// 同一时间同一周中不含有相关的实验名时，存入当前周的列
        /// </summary>
        /// <param name="Week">周名</param>
        /// <param name="ShiYanOne">实验一</param>
        /// <param name="ShiYanTwo">实验二</param>
        /// <param name="BanJiId">班级id</param>
        public static void ShiYanChuLi(string Week, string ShiYanOne, string ShiYanTwo, int BanJiId)  //实验相关周调换
        {
            //清空当前班级原第十七列使实验
            string sql = "update tb_NoClass set Week17='' where ClassId='{0}'";
            sql = string.Format(sql, BanJiId);
            Helpsql.getxiu(sql);
            for (int i = 1; i < 3; i++)
            {
                //添加当前班级对应周的实验
                sql = "update tb_NoClass set {0}='{1}' where ClassId='{2}' and GroupId='{3}' ";
                sql = string.Format(sql, Week, ShiYanOne, BanJiId,i);
                Helpsql.getxiu(sql);
            }
        }

        /// <summary>
        /// 两列周上的实验数交换
        /// </summary>
        /// <param name="zhou">要与第十七周调换的列</param>
        /// <param name="TimeId"></param>
        public static void SHUJUJIOAHUAN(int zhou,int TimeId) 
        {
            string week = "Week" + zhou;
            //构造两列周实验数的sql语句
            string sql = "update tb_NoClass set {0}=Week17,Week17={0} where TimeId='{1}' ";
            sql = string.Format(sql,week,TimeId);
            Helpsql.getxiu(sql);
        }

        #endregion


        /// <summary>
        /// 判断第十七周是否还有实验
        /// </summary>
        /// <param name="id">时间Id</param>
        /// <returns></returns>
        #region
        public static int panDuanShiYan(string id)
        {
            string sql = "select count(*) from tb_NoClass where TimeId='{0}' and Week17 !=''";
            sql = string.Format(sql,id);
            int n = Helpsql.getshu(sql);
            return n;
        }
        #endregion

        /// <summary>
        /// 排课离散
        /// </summary>
        #region
        public static void FourLie()
        {
            string sql = "update tb_NoClass set Week3=Week8,Week8=Week3,Week11=Week15,Week15=Week11 ";
            Helpsql.getxiu(sql);
        }
        #endregion
    }
}
